Skip Headers

Oracle® Transparent Gateway for DRDA Installation and User's Guide 10g Release 2 (10.2) for Microsoft Windows Part Number B16218-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# G Sample Applications

This appendix contains sample applications that can be used with the gateway:

- DB2INS

- ORAIND

## DB2INS

`DB2INS` is a sample DB2 stored procedure that inserts a row into a DB2 table. This procedure uses the SIMPLE linkage convention.

    /*********************************************************************/
    /*                                                                   */
    /* This DB2 stored procedure inserts values for the DNAME and LOC    */
    /* columns of DB2 user table SCOTT.DEPT.                             */
    /*                                                                   */
    /* The SCOTT.DEPT table is defined to DB2 as                         */
    /*       DEPTNO INTEGER, DNAME CHAR(14), LOC VARCHAR(13).            */
    /*                                                                   */
    /* This procedure receives 3 input parameters from the calling       */
    /* program which contain the values to insert for DEPTNO, DNAME, and */
    /* LOC.                                                              */
    /*                                                                   */
    /* The linkage convention used for this stored procedure is SIMPLE.  */
    /*                                                                   */
    /* The output parameter for this procedure contains the SQLCODE from */
    /* the INSERT operation.                                             */
    /*                                                                   */
    /* The entry in the DB2 catalog table SYSIBM.SYSPROCEDURES for this  */
    /* stored procedure might look like this:                            */
    /*                                                                   */
    /* INSERT INTO SYSIBM.SYSPROCEDURES                                  */
    /*   (PROCEDURE, AUTHID, LUNAME, LOADMOD, LINKAGE, COLLID, LANGUAGE, */
    /*    ASUTIME, STAYRESIDENT, IBMREQD, RUNOPTS, PARMLIST)             */
    /* VALUES                                                            */
    /*   ('DB2INS',  ' ', ' ', 'DB2INS', ' ', 'DB2DEV', 'C', '0', ' ',   */
    /*    'N', ' ', 'A INT IN, B CHAR(14) IN, C VARCHAR(13) IN,          */
    /*    D INT OUT, E CHAR(10) OUT');                                   */
    /*********************************************************************/
    #pragma runopts(plist(os))
    #include <stdlib.h>
      EXEC SQL INCLUDE SQLCA;
    /*********************************************************************/
    /* Declare C variables for SQL operations on the parameters.  These  */
    /* are local variables to the C program which you must copy to and   */
    /* from the parameter list provided to the stored procedure.         */
    /*********************************************************************/

      EXEC SQL BEGIN DECLARE SECTION;
      long dno;               /* input parm - DEPTNO */
      char dname[15];         /* input parm - DNAME  */
      char locale[14];        /* input parm - LOC    */
      EXEC SQL END DECLARE SECTION;
    main(argc,argv)
      int argc;
      char *argv[];
    {
    /*********************************************************************/
    /* Copy the input parameters into the area reserved in the local     */
    /* program for SQL processing.                                       */
    /*********************************************************************/
       dno = *(int *) argv[1];
       strcpy(dname, argv[2]);
       strcpy(locale, argv[3]);
    /*********************************************************************/
    /* Issue SQL INSERT to insert a row into SCOTT.DEPT                  */
    /*********************************************************************/
     EXEC SQL INSERT INTO SCOTT.DEPT VALUES(:dno, :dname, :locale);
    /*********************************************************************/
    /* Copy SQLCODE to the output parameter list.                        */
    /*********************************************************************/
       *(int *) argv[4] = SQLCODE;
    }

## ORAIND

`ORAIND` is a sample host program that calls a DB2 stored procedure (DB2INS) to insert a row into a DB2 table.

    /*********************************************************************/
    /* This sample ProC program calls DB2 stored procedure DB2INS to     */
    /* insert values into the DB2 user table SCOTT.DEPT.  This calling   */
    /* program uses embedded PL/SQL to call the stored procedure.        */
    /*********************************************************************/
    #include <stdio.h>
    EXEC SQL BEGIN DECLARE SECTION;
            VARCHAR         username[20];
            VARCHAR         password[20];
            int             dept_no;
            char            dept_name[14];
            VARCHAR         location[13];
            int             code;
            char            buf[11];
            int             x;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL INCLUDE SQLCA;
    main()
    {
    /*********************************************************************/
    /* Setup Oracle user id and password                                  */
    /*********************************************************************/
      strcpy(username.arr, "SCOTT");          /* copy the username */
      username.len = strlen(username.arr);
      strcpy(password.arr, "TIGER");          /* copy the password */
      password.len = strlen(password.arr);
      EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
    /*********************************************************************/
    /* Logon to Oracle                                                   */
    /*********************************************************************/
      EXEC SQL CONNECT :username IDENTIFIED BY :password;
      printf("\nConnected to ORACLE as user: %s\n", username.arr);
      /* Delete any existing rows from DB2 table */
      EXEC SQL DELETE FROM SCOTT.DEPT@GTWLINK;
      EXEC SQL COMMIT;

    /*------------------------ begin pl/sql block -----------------------*/
    /*********************************************************************/
    /* Insert 1 row into DB2 table SCOTT.DEPT by invoking DB2 stored     */
    /* procedure DB2INS.  The DB2 stored procedure will perform the      */
    /* INSERT.                                                           */
    /*                                                                   */
    /* SCOTT.DEPT table is defined on DB2 as:                            */
    /*                                                                   */
    /*    DEPTNO    INTEGER;                                             */
    /*    DNAME     CHAR(14);                                            */
    /*    LOC       VARCHAR(13);                                         */
    /*                                                                   */
    /*********************************************************************/
      EXEC SQL EXECUTE
      BEGIN
         :dept_no := 10;
         :dept_name := 'GATEWAY';
         :location := 'ORACLE';
         DB2INS@GTWLINK(:dept_no, :dept_name, :location, :code);
      END;
      END-EXEC;
    /*------------------------- end pl/sql block ------------------------*/
    /*********************************************************************/
    /* Check the SQLCODE returned from the stored procedures INSERT.     */
    /*********************************************************************/
      if (code == 0)
        printf("DB2INS reports successful INSERT\n");
      else
      {
        printf("DB2INS reports error on INSERT.\nSQLCODE=%d\n",code);
        goto sqlerror;
      }
    /*********************************************************************/
    /* Verify row insertion.  Query the data just inserted.              */
    /*********************************************************************/
       EXEC SQL SELECT deptno, dname, loc INTO
          :dept_no, :dept_name, :location
          FROM SCOTT.DEPT@GTWLINK WHERE deptno = 10;
       printf("\nData INSERTed was:\n");
       printf("\ndeptno = %d, dname = %s, loc = %s\n",
              dept_no, dept_name, location.arr);
    /*********************************************************************/
    /* Logoff from Oracle                                                */
    /*********************************************************************/
      EXEC SQL COMMIT RELEASE;
      printf("\n\nHave a good day\n\n");
      exit(0);
      sqlerror:
        printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
        EXEC SQL WHENEVER SQLERROR CONTINUE;
        EXEC SQL ROLLBACK RELEASE;
        exit(1);
    }